clear
set maxvar 120000 


/*------------------------
B. FX
--------------------------*/

//Last run March 19, 2024. WDI online subject to change, so this analysis file is saved as data.
/*loc run = 0
if `run' == 1{

wbopendata, indicator(SP.POP.TOTL; NY.GDP.PCAP.PP.KD; FP.CPI.TOTL; PA.NUS.FCRF) long clear

rename sp_pop_totl POPwdi
rename ny_gdp_pcap_pp_kd GDPPCAPPPPwdi
rename fp_cpi_totl CPIwdi
rename pa_nus_fcrf FXwdi

keep POP GDP CPI FX countrycode year
sort countrycode year

save "$data\wdi_inflation.dta", replace

}
*/

loc run = 1
if `run' == 1{

import excel "$data\ICP2017-Global-Researcher-Data__DECTI_TR_0727-2020.xlsx", sheet("EXR") cellrange(A5:N221) firstrow clear

rename H LCU_USD2011
rename I LCU_USD2012
rename J LCU_USD2013
rename K LCU_USD2014
rename L LCU_USD2015
rename M LCU_USD2016
rename N LCU_USD2017

drop IndicatorCode

rename CountryCode countrycode

reshape long LCU_USD, i(countrycode CountryName ICP2017* Currency*) j(year)

sort countrycode year

keep year countrycode LCU_USD

save "$temp\fx.dta", replace

}


/*------------------------
B. Average Prices 2011 and 2017
--------------------------*/

loc run = 1
if `run' == 1{

//2011
import excel "$data/ICP2017-Global-Researcher-Data__DECTI_TR_0727-2020.xlsx", sheet("APG_2011") cellrange(A4:HF8763) firstrow clear

rename B ItemName
rename A ItemCode
rename C Year

drop GB-HF
foreach var of varlist DZA-VGB{
	rename `var' ap_`var'
}
drop if ItemCode == "Item Code" | ItemCode==""
destring Year, replace
rename Year year

reshape long ap_, i(year ItemCode ItemName) j(countrycode) string

save "$temp\icp_2011_ap.dta", replace

}

loc run = 1
if `run' == 1{

//2017
import excel "$data/ICP2017-Global-Researcher-Data__DECTI_TR_0727-2020.xlsx", sheet("APG_2017") cellrange(A4:GC965)  firstrow clear

drop CountryCode A
rename C ItemName
rename B  ItemCode
rename D  Year

foreach var of varlist AGO-UZB{
	rename `var' ap_`var'
}
drop if ItemCode == "Item Code" | ItemCode==""
destring Year, replace
rename Year year

reshape long ap_, i(year ItemCode ItemName) j(countrycode) string

save "$temp\icp_2017_ap.dta", replace

}

loc run =1
if `run' == 1{
	
use "$temp\icp_2017_ap.dta"
append using "$temp\icp_2011_ap.dta"
	
gen substr = substr(ItemCode,-3,3) if year==2011
replace ItemCode = substr(ItemCode,1,9) if substr=="000"

preserve
keep if year==2011
keep ItemCode ItemName
duplicates drop
save "$temp\icp_ap_label.dta", replace
restore

keep countrycode ItemCode ap_ year

egen id = group(ItemCode countrycode)
bys id: egen count = count(id)
keep if count == 2

drop count 
merge m:1 ItemCode using "$temp\icp_ap_label.dta"
drop if _m==2
drop _m

order id ItemCode ItemName countrycode year 
compress
save "$data\icp_ap_clean.dta", replace
}



